// ================================================================================
// 		File: PartyJoinApplycation.cs
// 		Desc: 
//  
// 		Called by:   
//               
// 		Auth: chenwenzhi（--）
// 		Date: 2015-12-28 23:59
// ================================================================================
// 		Change History
// ================================================================================
// 		Date:		Author:				Description:
// 		--------	--------			-------------------
//    
// ================================================================================
//
// ================================================================================
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace ZHDJ.Data.Models.JoinParty
{
    /// <summary>
    /// 
    /// </summary>   
    public class PartyJoinApplycation
    {
        #region 属性
        [Key]
        public string AppllicationID { get; set; }
        /// <summary>
        /// LoginID
        /// </summary>
        [StringLength(128)]
        public string LoginID { get; set; }

        /// <summary>
        /// UserName
        /// </summary>
        [StringLength(128)]
        public string UserName { get; set; }

        /// <summary>
        /// Content
        /// </summary>
        [StringLength(8000)]
        public string Content { get; set; }

        /// <summary>
        /// StudyyAndWorkCompany
        /// </summary>
        [StringLength(4000)]
        public string StudyyAndWorkCompany { get; set; }

        /// <summary>
        /// WorkAddress
        /// </summary>
        [StringLength(1000)]
        public string WorkAddress { get; set; }

        /// <summary>
        /// CreateTime
        /// </summary>

        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// Status(0:草稿，1：申请中，2：申请成功，3：申请取消)
        /// </summary>
        public int Status { get; set; }

        /// <summary>
        /// BranchID
        /// </summary>
        [StringLength(128)]
        public string BranchID { get; set; }

        /// <summary>
        /// CreateUserID
        /// </summary>
        [StringLength(128)]
        public string CreateUserID { get; set; }

        /// <summary>
        /// 本人成分
        /// </summary>
        public string Nature { get; set; }

        /// <summary>
        /// 曾用名
        /// </summary>
        public string BeforeName { get; set; }

        #endregion

        public static DataSet GetGridData(string UserName, string BranchID, string CreateUserID, string PageSign, string status = "", bool isAdmin = false)
        {
            StringBuilder sbSql = new StringBuilder();

            if (!string.IsNullOrEmpty(BranchID))
            {
                sbSql.AppendFormat("call f_BranchID('{0}');", BranchID);
            }

            sbSql.Append(@"SELECT '' as UserNameEx,data.AppllicationID,data.LoginID,data.UserName,data.Content,data.StudyyAndWorkCompany,data.WorkAddress,data.CreateTime,data.Status,
                        pom.UserName as UName,
                        case pom.Gender when 0 then '男' when 1 then '女' else '' end as Gender,
                        case data.status WHEN 1 THEN '申请中' WHEN 0 THEN '草稿' WHEN 2 THEN '申请成功' ELSE '申请取消' END as StatusName,
                        pom.IdentityCard,pom.Nation,pom.NativePlace,pom.Educational,pom.MobileNo,pom.Address,pom.WorkAddress,pob.BranchName,
                        data.BranchID,'' as CurrentStepID,'' as StandID,
(SELECT CurrentStepName FROM tblpartyjoinlinkexample where AppllicationID=`data`.AppllicationID ORDER BY CreateTime DESC LIMIT 0,1)  as CurrentStand
                         from tblPartyJoinApplycation as data
                        left join tblPartyOrgMember as pom on data.LoginID=pom.ID
                        left join tblPartyOrgBranch as pob on data.BranchID=pob.BranchID
                        where 1=1");
            if (!string.IsNullOrEmpty(UserName))
            {
                sbSql.AppendFormat(" and pom.UserName like '%{0}%'", UserName);
            }
            if (!string.IsNullOrEmpty(BranchID))
            {
                sbSql.AppendFormat(" and data.BranchID in (select ID from tb_level) ");
            }
            if (!string.IsNullOrEmpty(CreateUserID))
            {
                if (!isAdmin)
                    sbSql.AppendFormat(" and (data.CreateUserID like '%{0}%' or data.LoginID like '%{0}%' or pom.UserName like '%{0}%')", CreateUserID);
            }
            if (!string.IsNullOrEmpty(status))
            {
                sbSql.AppendFormat(" and data.Status={0}", status);
            }
            //排序
            sbSql.AppendFormat(" order by data.CreateTime desc");

            return MySqlHelper.ExecuteDataSet(sbSql.ToString());
        }
        public static DataSet GetGridData(string UserName, string BranchID, string CreateUserID, int pageIndex, int pageSize, string status = "", bool isAdmin = false)
        {
            StringBuilder sbSql = new StringBuilder();

            if (!string.IsNullOrEmpty(BranchID))
            {
                sbSql.AppendFormat("call f_BranchID('{0}');", BranchID);
            }

            sbSql.Append(@"SELECT SQL_CALC_FOUND_ROWS '' as UserNameEx,data.AppllicationID,data.LoginID,data.UserName,data.Content,data.StudyyAndWorkCompany,data.WorkAddress,data.CreateTime,data.Status,
                        pom.UserName as UName,
                        case pom.Gender when 0 then '男' when 1 then '女' else '' end as Gender,
                        case data.status WHEN 1 THEN '申请中' WHEN 0 THEN '草稿' WHEN 2 THEN '申请成功' ELSE '申请取消' END as StatusName,
                        pom.IdentityCard,pom.Nation,pom.NativePlace,pom.Educational,pom.MobileNo,pom.Address,pom.WorkAddress,pob.BranchName,
                        data.BranchID,'' as CurrentStepID,'' as StandID,
(SELECT CurrentStepName FROM tblpartyjoinlinkexample where AppllicationID=`data`.AppllicationID ORDER BY CreateTime DESC LIMIT 0,1)  as CurrentStand
                         from tblPartyJoinApplycation as data
                        left join tblPartyOrgMember as pom on data.LoginID=pom.ID
                        left join tblPartyOrgBranch as pob on data.BranchID=pob.BranchID
                        where 1=1");
            if (!string.IsNullOrEmpty(UserName))
            {
                sbSql.AppendFormat(" and pom.UserName like '%{0}%'", UserName);
            }
            if (!string.IsNullOrEmpty(BranchID))
            {
                sbSql.AppendFormat(" and data.BranchID in (select ID from tb_level) ");
            }
            if (!string.IsNullOrEmpty(CreateUserID))
            {
                if (!isAdmin)
                    sbSql.AppendFormat(" and (data.CreateUserID like '%{0}%' or data.LoginID like '%{0}%' or pom.UserName like '%{0}%')", CreateUserID);
            }
            if (!string.IsNullOrEmpty(status))
            {
                sbSql.AppendFormat(" and data.Status={0}", status);
            }
            //排序
            sbSql.AppendFormat(" order by data.CreateTime desc limit {0} ,{1};SELECT FOUND_ROWS();", pageIndex * pageSize, pageSize);

            return MySqlHelper.ExecuteDataSet(sbSql.ToString());
        }
        /// <summary>
        /// 判断是否已经申请
        /// </summary>
        /// <returns></returns>
        public static bool IsApplyOver(string applyID)
        {

            string sql = "select count(*) from tblPartyJoinLinkExample where AppllicationID='" + applyID + "' ";

            return MySqlHelper.ExecuteNonQuery(sql) > 0;

        }

        /// <summary>
        /// 通过用户ID获取入党申请时间
        /// </summary>
        /// <param name="LoginID">用户登陆ID</param>
        /// <returns></returns>
        public static string GetApplyTimeByLoginID(string LoginID)
        {
            string ApplyTime = string.Empty;
            string sql = string.Format("select CreateTime from tblPartyJoinApplycation where LoginID='{0}' order by CreateTime desc LIMIT 1", LoginID);

            object obj = MySqlHelper.ExecuteScalar(sql);
            if (obj != null)
            {
                ApplyTime = string.Format("{0:yyyy-MM-dd}", obj);
            }
            return ApplyTime;
        }

        /// <summary>
        /// 通过用户ID获取入党申请ID（非申请取消的）
        /// </summary>
        /// <param name="LoginID"></param>
        /// <returns></returns>
        public static string GetApplyIDByLoginID(string LoginID)
        {
            string applyId = string.Empty;
            string sql = string.Format(@"select AppllicationID from tblPartyJoinApplycation where LoginID='{0}' and Status in (0,1) order by CreateTime desc limit 0,1", LoginID);
            object obj = MySqlHelper.ExecuteScalar(sql);
            if (obj != null)
            {
                applyId = obj.ToString();
            }
            return applyId;
        }

        /// <summary>
        /// 通过用户ID获取入党申请ID
        /// </summary>
        /// <param name="LoginID"></param>
        /// <returns></returns>
        public static string GetApplyIDNoParamByLoginID(string LoginID)
        {
            string applyId = string.Empty;
            string sql = string.Format(@"select AppllicationID from tblPartyJoinApplycation where LoginID='{0}' order by CreateTime desc limit 0,1", LoginID);
            object obj = MySqlHelper.ExecuteScalar(sql);
            if (obj != null)
            {
                applyId = obj.ToString();
            }
            return applyId;
        }
    }
}
